package dao.mysql;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.FacilityDTO;



import model.FacilitySearchDTO;
import dao.DAOException;
import dao.FacilityDAO;
import dao.mysql.util.*;


public class FacilityDAOImpl implements FacilityDAO{
	
	//private Connection conn ;
	public ArrayList<FacilityDTO> getAll() throws DAOException {
		// TODO Auto-generated method stub
		Connection conn = null;
		try {
			conn = MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		FacilityDTO facility;
	    ArrayList<FacilityDTO> list= new ArrayList<FacilityDTO>();
		try{
			
			conn.setAutoCommit(false);
			String sql = "select*from Facility";
			PreparedStatement prep = conn.prepareStatement(sql);
			ResultSet rs=prep.executeQuery();
			while(rs.next())
			{
				facility = new FacilityDTO();
				facility.setName(rs.getString("Name"));
				facility.setType(rs.getString("Type"));
				facility.setDescription(rs.getString("Description"));
				facility.setSeatingCapacity(rs.getInt("SeatingCapacity"));
				facility.setStatus(rs.getInt("Status"));
				list.add(facility);
			}				
				
				conn.commit();
				prep.close();
				
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
			
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}	
		return list;

	}
	
	public FacilityDTO getFacility(String name) throws DAOException {
		// TODO Auto-generated method stub
		Connection conn = null;
		try {
		     conn = MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		FacilityDTO facility=null;
		
		try{
			
			
			String sql = "select*from Facility where Name=? ";
			PreparedStatement preq = conn.prepareStatement(sql);
			preq.setString(1, name);
			ResultSet rs=preq.executeQuery();
			if(rs.next())
			{
				facility = new FacilityDTO();
				facility.setName(rs.getString("Name"));
				facility.setType(rs.getString("Type"));
				facility.setDescription(rs.getString("Description"));
				facility.setSeatingCapacity(rs.getInt("SeatingCapacity"));
				facility.setStatus(rs.getInt("Status"));
			}
		
				preq.close();
				
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
			
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}	
			return facility;
		}
	
	public void updateFacility(FacilityDTO facility) throws DAOException {
		// TODO Auto-generated method stub
		Connection conn = null;
		
		try {
			conn= MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			
			conn.setAutoCommit(false);
			String sql = "Update Facility " + 
			             "   Set Description= ? " +
			             " , Type= ? " +
					     " , SeatingCapacity= ? " +
			             " , Status= ? " +
					     " Where Name = ? ";
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, facility.getDescription());
			prep.setString(2, facility.getType());
			prep.setInt(3, facility.getSeatingCapacity());
			prep.setInt(4, facility.getStatus());
			prep.setString(5, facility.getName());
		
			prep.executeUpdate();

			conn.commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	@SuppressWarnings("null")
	public void insertFacility(FacilityDTO data) throws DAOException {
		Connection conn = null;
		
		try {
			conn=MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			conn.setAutoCommit(false);
			String sql = "Insert Into Facility Values (?,?,?,?,?) ";
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1,  data.getName());
			prep.setString(2,  data.getDescription());
			prep.setString(3,  data.getType());
			prep.setInt(4,     data.getSeatingCapacity());
			prep.setInt(5, data.getStatus());
			prep.executeUpdate();
			
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + sql);
		
			conn.commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	
	@SuppressWarnings("null")
	public ArrayList<FacilityDTO> findFacilityByCriteria(FacilitySearchDTO fsd)
			throws DAOException {
		Connection conn = null;
		
		try {
		  conn = MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		ArrayList<FacilityDTO> list = new ArrayList<FacilityDTO>();
		try {
			conn.setAutoCommit(false);
			String sql = "";
			String where = "";
			ArrayList<String> params = new ArrayList<String>();
			
			sql = "SELECT * FROM Facility ";
			
			if (fsd.getName() != null && !fsd.getName().isEmpty() ) {
				where = " WHERE ";
				where += " Name LIKE ? ";
				params.add(fsd.getName());
			} 
			if (fsd.getDescription() != null && !fsd.getDescription().isEmpty() ) {
				if ( where.isEmpty() ) where = " WHERE "; else where += " AND ";
				where += " Description LIKE ? ";
				params.add(fsd.getDescription());
				
			}
		
			
			PreparedStatement prep = conn.prepareStatement(sql + where);
			 for (int i = 1;i <=params.size();i ++){
				 prep.setString(i, "%" + params.get(i-1) + "%");
			 }
			 
			
			
			
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + sql+where);
			ResultSet rs = prep.executeQuery();
			while (rs.next()) {
				FacilityDTO facility = new FacilityDTO();
				facility.setName(rs.getString("Name"));
				facility.setDescription(rs.getString("Description"));
				facility.setType(rs.getString("Type"));
				facility.setSeatingCapacity(rs.getInt("SeatingCapacity"));
				facility.setStatus(rs.getInt("Status"));
				
				System.out.println(facility.toString());
				list.add(facility);
			}
			conn.commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return list;
	}
	
	public void deleteFacility(FacilityDTO facility) throws DAOException {
//		String deleteSql = "DELETE FROM heros WHERE name='"
//				+ hero.getName().trim() + "';";
		Connection conn = null;
		try {
			conn = MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		try {
			
			conn.setAutoCommit(false);
			String sql = "DELETE FROM Facility where Name= ? ";
			PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, facility.getName());
			
			prep.executeUpdate();
			
			conn.commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			System.out.println("#$#$#@$#@#@$#@$#@$#$#@");
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
}
